package projectx.db;

import java.sql.Date;
import java.sql.Time;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;

import projectx.util.CollectionUtil;
import projectx.util.StringUtil;

public class DbUtil {

	// --------------------------------------------------------------------------
	// S T A T I C M E N B E R S
	//
	// --------------------------------------------------------------------------
	public static final int DB_USERID_LENGTH = 8;

	private static final SimpleDateFormat SQL_DATE_FORMATTER = new SimpleDateFormat(
			"yyyy-MM-dd");

	// --------------------------------------------------------------------------
	// S T A T I C M E T H O D S
	//
	// --------------------------------------------------------------------------

	public static String chopId(String userId) {

		String tempUserId = userId;
		if (StringUtil.isBlank(tempUserId)) {
			return tempUserId;
		}

		tempUserId = tempUserId.toUpperCase();

		if (tempUserId.length() <= DB_USERID_LENGTH) {
			return tempUserId;
		}

		int index = tempUserId.indexOf('@');
		if (index < 0) {
			return tempUserId.substring(0, DB_USERID_LENGTH);
		}

		String shortName = tempUserId.substring(0, index);

		if (shortName.length() > DB_USERID_LENGTH) {
			shortName = shortName.substring(0, DB_USERID_LENGTH);
		}

		return shortName;
	}

	public static java.sql.Date getSqlDate(String date8) {

		if (date8 == null || date8.length() != 8) {
			return null;
		}

		return java.sql.Date.valueOf(date8.substring(0, 4) + '-' + // yyyy
				date8.substring(4, 6) + '-' + // MM
				date8.substring(6, 8)); // dd
	}

	public static synchronized java.sql.Date getSqlDate() {

		return getSqlDate(new java.util.Date());
	}

	public static synchronized java.sql.Date getSqlDate(java.util.Date date) {

		return java.sql.Date.valueOf(SQL_DATE_FORMATTER.format(date));
	}

	public static Timestamp getSqlTimestamp(java.util.Date date) {

		return new Timestamp(date.getTime());
	}

	public static Timestamp getSqlTimestamp(String date8, String time6) {

		if (date8 == null || date8.length() != 8) {
			return null;
		}

		if (time6 == null || time6.length() != 6) {
			return null;
		}

		// yyyy-mm-dd hh:mm:ss.fffffffff
		StringBuffer stringBuffer = new StringBuffer();
		stringBuffer.append(date8.substring(0, 4)); // yyyy
		stringBuffer.append("-");
		stringBuffer.append(date8.substring(4, 6)); // MM
		stringBuffer.append("-");
		stringBuffer.append(date8.substring(6, 8)); // dd
		stringBuffer.append(" ");
		stringBuffer.append(time6.substring(0, 2)); // hh
		stringBuffer.append(":");
		stringBuffer.append(time6.substring(2, 4)); // mm
		stringBuffer.append(":");
		stringBuffer.append(time6.substring(4, 6)); // ss
		stringBuffer.append(".000000000");

		return Timestamp.valueOf(stringBuffer.toString());
	}

	public static boolean hasValue(String value) {

		return (value != null && value.length() > 0) ? true : false;
	}

	public static boolean hasValue(Date value) {

		return (value != null) ? true : false;
	}

	public static boolean hasValue(Timestamp value) {

		return (value != null) ? true : false;
	}

	public static void appendSql(StringBuffer sb, String sql,
			java.sql.Date value) {

		if (value != null) {
			privateAppendSql(sb, sql);
		}
	}

	public static void appendSql(StringBuffer sb, String sql,
			java.sql.Timestamp value) {

		if (value != null) {
			privateAppendSql(sb, sql);
		}
	}

	public static void appendSql(StringBuffer sb, String sql, String fromValue,
			String toValue) {

		if (hasValue(fromValue) && hasValue(toValue)) {
			appendSql(sb, sql + " >= ? ", fromValue);
			appendSql(sb, sql + " <= ? ", toValue);
		}
		if (hasValue(fromValue) && !hasValue(toValue)) {
			appendSql(sb, sql + " = ? ", fromValue);
		}
	}

	public static void appendSql(StringBuffer sb, String sql, String value) {

		if (value != null && value.length() > 0) {

			privateAppendSql(sb, sql);
		}
	}

	private static void privateAppendSql(StringBuffer sb, String sql) {

		if (sb.indexOf(" WHERE") >= 0) {
			sb.append(" AND ");
		} else {
			sb.append(" WHERE ");
		}

		sb.append(sql);
	}

	public static void appendSql(StringBuffer sb, String sql, String[] value) {

		if (CollectionUtil.isEmpty(value)) {
			privateAppendSql(sb, sql);
		}
	}

	public static Time getSqlTime(java.util.Date date) {
		return new Time(date.getTime());
	}

	public static Time getSqlTime(String time6) {

		if (time6 == null || time6.length() != 6) {
			return null;
		}

		return java.sql.Time.valueOf(time6.substring(0, 2) + ':' + // hh
				time6.substring(2, 4) + ':' + // mm
				time6.substring(4, 6)); // ss
	}
}
